from datetime import datetime

import pandas as pd
import re
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter

from bct.tools.client.bct4_client import BCT4Client, BCTVer
from bct_config import Bct4Hoster

# 如果以下参数都有值时, 优先使用交易编号模式
# 交易编号
param_trade_ids = []
# 根据日期查询
initial_date_begin = '2022-03-01'
initial_date_end = '2022-03-17'

date_regex = '^([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-' \
             '(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|' \
             '((0[469]|11)-(0[1-9]|[12][0-9]|30))|' \
             '(02-(0[1-9]|[1][0-9]|2[0-8])))$'


def get_trades_by_ids(trade_ids):
    all_trades = client.find_trades({'tradeIds': trade_ids})
    for trade in all_trades:
        events = client.find_events_by_trade_id(trade.get('tradeId'))
        trade["events"] = events
    wrap_pricing_trades(all_trades)
    return convert(all_trades)


def get_trades_by_date(begin_date, end_date):
    if re.match(date_regex, begin_date) is None or re.match(date_regex, end_date) is None:
        print("日期格式不正确, 参考格式:2022-01-01")
        return []
    all_trades = client.find_trades({'initialDateFrom': begin_date, 'initialDateTo': end_date})
    for trade in all_trades:
        events = client.find_events_by_trade_id(trade.get('tradeId'))
        trade["events"] = events
    wrap_pricing_trades(all_trades)
    return convert(all_trades)


def wrap_pricing_trades(all_trades):
    trade_ids = [trade.get('tradeId') for trade in all_trades]
    pricing_trades = client.find_pricing_trades({'tradeIds': trade_ids})
    pricing_trade_ids = [pricing_trade.get('tradeId') for pricing_trade in pricing_trades]
    pricing_trade_map = dict(zip(pricing_trade_ids, pricing_trades))
    for trade in all_trades:
        trade["pricing_trade"] = pricing_trade_map.get(trade.get('tradeId'))


def convert(bct4_trade_list):
    export_trades = []

    for bct4_trade in bct4_trade_list:
        trade_id = bct4_trade.get('tradeId')
        print(bct4_trade.get('tradeId'))
        product = bct4_trade['products'][0]
        customize = product.get('customize')
        # OPTION, INCOME_CERTIFICATE
        # 业务类型
        business_type = bct4_trade.get('businessType')
        # 产品结构
        product_type = bct4_trade.get('productType')
        # 买卖方向
        direction = bct4_trade.get('direction')
        # 前端期权费率
        initial_premium_rate = 0 if bct4_trade.get('initialPremiumRate') is None else bct4_trade.get('initialPremiumRate')
        # 后端期权费率
        final_premium_rate = 0 if bct4_trade.get('finalPremiumRate') is None else bct4_trade.get('finalPremiumRate')
        # 内部资金成本(收益凭证) TODO 目前该字段类型为字符串需要处理, 后续生产版本类型待确定
        internal_premium_rate = 0
        sw_option_type = ""
        # 是否场外完全抵消
        off_set = ''
        # 股份性质
        share_limit = ''
        if customize is not None:
            # internal_premium_rate = customize.get('internalPremiumRate')
            # 处理字符串
            if customize.get('internalPremiumRate') is not None and type(customize.get('internalPremiumRate')) == str:
                internal_premium_rate = float(customize.get('internalPremiumRate').replace('%', '')) / 100.0
            if customize.get('mtcOptionType') is not None:
                sw_option_type = customize.get('mtcOptionType')
            if customize.get('offSet') is not None:
                off_set = customize.get('offSet')
            if customize.get('shareLimit') is not None:
                share_limit = customize.get('shareLimit')

        # 最低收益率
        minimum_coupon_rate = 0 if product.get('minimumCouponRate') is None else product.get('minimumCouponRate')

        touch_price_info = __handle_touch_price(bct4_trade)
        participation_ratio_info = __handle_participation_ratio(bct4_trade)
        rebate_info = __handle_rebate(bct4_trade)

        trade_info = {'ContractId': bct4_trade.get('tradeConfirmationId')}
        # 若业务类型为场外期权，后端期权费不为空或0，则为【收益互换】，反之，为【场外期权】
        # 若业务类型为收益凭证，此值填充【收益互换凭证】
        if business_type == 'OPTION':
            if final_premium_rate == 0:
                trade_info['ContractType'] = '场外期权'
            else:
                trade_info['ContractType'] = '收益互换'
        else:
            trade_info['ContractType'] = '收益互换凭证'

        trade_info['CounterParty'] = bct4_trade.get('counterpartyName')
        trade_info['Code'] = product.get('instrumentId')
        trade_info['Pos'] = -1 if direction == 'BUYER' else 1
        trade_info['OptionType'] = sw_option_type
        if bct4_trade.get('crossBorder') is not None and bct4_trade.get('crossBorder') == 'COMPO1':
            # 若为compo1，需用名义本金额*期初汇率
            trade_info['Notamt'] = bct4_trade.get('initialNotional') * bct4_trade.get('initialXRate')
        else:
            trade_info['Notamt'] = bct4_trade.get('initialNotional')
        trade_info['BeginDate'] = datetime.strptime(product['initialDate'], '%Y-%m-%d').strftime('%Y/%m/%d')
        trade_info['EndDate'] = datetime.strptime(product['lastObservationDate'], '%Y-%m-%d').strftime('%Y/%m/%d')
        trade_info['DealDate'] = datetime.strptime(product['settlementDate'], '%Y-%m-%d').strftime('%Y/%m/%d')
        trade_info['Maturity'] = bct4_trade['countedDays']
        trade_info['YearDays'] = bct4_trade['annualizedDays']
        trade_info['IfActual'] = 0 if bct4_trade.get('annualized') else 1

        # 若业务类型为场外期权，若为卖，则取场外期权-交易列表-详情|合约期权费，若为买，则取场外期权-交易列表-详情|合约期权费*(-1)
        # 若业务类型为收益凭证，若为卖,则取场外期权-交易列表-详情|内部资金成本(收益凭证)+场外期权-交易列表-详情|最低收益率;若为买，则取详情|内部资金成本(收益凭证)-场外期权-交易列表-详情|最低收益率
        if business_type == 'OPTION':
            if direction == 'BUYER':
                trade_info['TotalPremium'] = (initial_premium_rate + final_premium_rate) * -1
            else:
                trade_info['TotalPremium'] = initial_premium_rate + final_premium_rate
        else:
            if direction == 'BUYER':
                trade_info['TotalPremium'] = internal_premium_rate - minimum_coupon_rate
            else:
                trade_info['TotalPremium'] = internal_premium_rate + minimum_coupon_rate

        if direction == 'BUYER':
            trade_info['MimumPremium'] = minimum_coupon_rate * -1
        else:
            trade_info['MimumPremium'] = minimum_coupon_rate

        trade_info['Premium'] = trade_info['TotalPremium'] - minimum_coupon_rate
        trade_info['InitialS'] = product['initialPrice']

        trade_info['Pratio'] = participation_ratio_info['participation_ratio']

        trade_info['LstrikeRatio'] = touch_price_info['low_strike_ratio']
        trade_info['HstrikeRatio'] = touch_price_info['high_strike_ratio']
        trade_info['LtouchRatio'] = touch_price_info['low_touch_ratio']
        trade_info['HtouchRatio'] = touch_price_info['high_touch_ratio']
        trade_info['Rebate'] = rebate_info['rebate']
        trade_info['Rebate2'] = rebate_info['rebate2']
        trade_info['Rebate3'] = rebate_info['rebate3']
        # 初始预付金率
        trade_info['Rebate4'] = 0 if bct4_trade['initialPrePayRatio'] is None else bct4_trade['initialPrePayRatio']
        trade_info['Pratio1'] = participation_ratio_info['participation_ratio1']
        trade_info['Pratio2'] = participation_ratio_info['participation_ratio2']

        trade_info['ObSerDate'] = None
        if product.get('observations') is not None:
            trade_info['ObSerDate'] = ''
            for observation_info in product.get('observations'):
                # 单鲨 双鲨 保本雪球看涨 保本雪球看跌
                # 非保本雪球 部分保本雪球 折价建仓雪球
                # 三元雪球 降落伞雪球
                # 部分保护折价建仓雪球 自动赎回看涨价差
                # 增强型雪球 部分保护增强型雪球
                # 部分保护红利增强型雪球
                # 阶梯敲入非保本雪球
                # 部分保护双敲入型雪球
                if product_type in ['SHARK_FIN', 'DOUBLE_SHARK_FIN', 'SNOWBALL_FLOATING_CALL', 'SNOWBALL_FLOATING_PUT',
                                    'SNOWBALL_FLOATING_NONE', 'SNOWBALL_FLOATING_PARTIAL', 'SNOWBALL_DISCOUNT_OPEN',
                                    'SNOWBALL_DOUBLE_DIGITAL', 'SNOWBALL_PARACHUTE',
                                    'SNOWBALL_DISCOUNT_OPEN_PARTIAL_PROTECTIVE', 'SNOWBALL_AUTO_REDEEM_SPREAD_CALL',
                                    'SNOWBALL_ENHANCE', 'SNOWBALL_ENHANCE_PARTIAL_PROTECTIVE',
                                    'SNOWBALL_ENHANCE_COUPON_RATE_PARTIAL_PROTECTIVE',
                                    'SNOWBALL_FLOATING_NONE_STEP_KNOCK_IN',
                                    'SNOWBALL_PARTIAL_PROTECTIVE_DOUBLE_KNOCKIN']:
                    observation_date = observation_info['knockOutObservationDate']
                else:
                    observation_date = observation_info['observationDate']

                trade_info['ObSerDate'] += \
                    datetime.strptime(observation_date, '%Y-%m-%d').strftime('%Y/%m/%d') + '\n'

        trade_info['SettleMethod'] = bct4_trade.get('priceType')
        initial_pre_pay_ratio = 0
        second_pre_pay_ratio = 0
        if bct4_trade.get('initialPrePayRatio') is not None:
            initial_pre_pay_ratio = bct4_trade.get('initialPrePayRatio')
        if bct4_trade.get('secondPrePayRatio') is not None:
            second_pre_pay_ratio = bct4_trade.get('secondPrePayRatio')

        # 初始预付金率+追加预付金率
        pre_pay_fee = initial_pre_pay_ratio + second_pre_pay_ratio
        trade_info['PrePayFee'] = -1 * pre_pay_fee if direction == 'BUYER' else pre_pay_fee
        trade_info['CrossBorder(跨境类型)'] = bct4_trade.get('crossBorder')
        trade_info['CrossBorder(交易货币)'] = bct4_trade.get('tradeCurrency')
        trade_info['ExchanRate'] = bct4_trade.get('initialXRate')
        trade_info['Pratio3'] = participation_ratio_info['participation_ratio3']
        trade_info['Pratio4'] = participation_ratio_info['participation_ratio4']
        if bct4_trade.get('markToMarket') == 'SINGLE':
            trade_info['CreditCover'] = 'Yes'
        else:
            trade_info['CreditCover'] = ''

        if bct4_trade.get('marginCalcMode') == '单笔保证金':
            trade_info['MarginType'] = 'SINGLE'
        elif bct4_trade.get('marginCalcMode') == '组合保证金':
            trade_info['MarginType'] = 'COMBO'
        else:
            trade_info['MarginType'] = ''

        trade_info['KnockInDate'] = None
        events = bct4_trade.get('events')
        for event in events:
            if event.get('eventType') == 'KNOCK_IN':
                trade_info['KnockInDate'] = datetime.strptime(event.get('knockInDate'), '%Y-%m-%d').strftime('%Y/%m/%d')
                break

        trade_info['PrePayFeeRevenue'] = 0 if bct4_trade.get('prePayCouponRate') is None else bct4_trade.get('prePayCouponRate')
        trade_info['SalesFee'] = 0 if bct4_trade.get('saleRate') is None else bct4_trade.get('saleRate')
        if bct4_trade.get('initialCouponAnnualized'):
            trade_info['ABSFrontPremium'] = 0
        else:
            trade_info['ABSFrontPremium'] = bct4_trade.get('initialCoupon')

        # 是否场外完全抵消
        trade_info['Offset'] = off_set
        trade_info['ShareLimit'] = share_limit

        if bct4_trade.get('finalCouponAnnualized'):
            trade_info['ABSBackPremium'] = 0
        else:
            trade_info['ABSBackPremium'] = bct4_trade.get('finalCoupon')

        # 分红日
        trade_info['ChangeDate'] = None
        for event in events:
            if event.get('eventType') == 'DIVIDEND':
                trade_info['ChangeDate'] = datetime.strptime(event.get('exDate'), '%Y-%m-%d').strftime('%Y/%m/%d')
                break

        trade_info['ActualEndDate'] = bct4_trade.get('pricing_trade').get('actualEndDate')

        # 开仓操作用户/时间
        trade_info['open_position_user'] = None

        trade_info['open_time'] = None
        trade_info['unwind_time'] = None
        trade_info['settle_time'] = None
        for event in events:
            if event.get('eventType') == 'OPEN':
                trade_info['open_position_user'] = event.get('createdBy')
                trade_info['open_time'] = event.get('createdAt')
            elif event.get('eventType') == 'UNWIND':
                trade_info['unwind_time'] = event.get('createdAt')
            elif event.get('eventType') == 'SETTLE':
                trade_info['settle_time'] = event.get('createdAt')

        export_trades.append(trade_info)
    return export_trades


def __handle_participation_ratio(bct4_trade):
    # 产品结构
    product_type = bct4_trade.get('productType')
    product = bct4_trade['products'][0]
    participation_ratio = None
    participation_ratio1 = None
    participation_ratio2 = None
    participation_ratio3 = None
    participation_ratio4 = None

    # 欧式香草 美式香草 欧式价差 鹰式 向下敲入看涨
    # 向下敲入看跌 欧式二元 美式二元触碰 美式二元不触碰 美式双触碰
    # 美式双不触碰 二元凹式 二元凸式 三层阶梯看涨 三层阶梯看跌
    # 四层阶梯看涨 四层阶梯看跌 区间累积 保护型看涨
    # 单层区间累积看涨 单层区间累积看跌 单鲨 双鲨
    # 区间增强 亚式看涨 亚式看跌 部分保本凤凰到期派息
    # 部分保本凤凰立即派息 非保本凤凰到期派息
    # 非保本凤凰立即派息 保本雪球看涨 保本雪球看跌
    # 非保本雪球 部分保本雪球 折价建仓雪球
    # 三元雪球 降落伞雪球 部分保护折价建仓雪球
    if product_type in ['VANILLA_EUROPEAN', 'VANILLA_AMERICAN', 'VERTICAL_SPREAD', 'EAGLE', 'KNOCK_IN_DOWN_CALL',
                        'KNOCK_IN_DOWN_PUT', 'DIGITAL_EUROPEAN', 'TOUCH', 'NO_TOUCH', 'DOUBLE_TOUCH',
                        'DOUBLE_NO_TOUCH', 'CONCAVE', 'CONVEX', 'DOUBLE_DIGITAL_CALL', 'DOUBLE_DIGITAL_PUT',
                        'TRIPLE_DIGITAL_CALL', 'TRIPLE_DIGITAL_PUT', 'RANGE_ACCRUALS', 'PROTECTIVE_CALL',
                        'RANGE_ACCRUALS_DIGITAL_CALL', 'RANGE_ACCRUALS_DIGITAL_PUT', 'SHARK_FIN', 'DOUBLE_SHARK_FIN',
                        'RANGE_ENHANCE', 'ASIAN_CALL', 'ASIAN_PUT', 'PHOENIX_FLOATING_PARTIAL_DUE_COUPON',
                        'PHOENIX_FLOATING_PARTIAL_IMMEDIATE_COUPON', 'PHOENIX_FLOATING_NONE_DUE_COUPON',
                        'PHOENIX_FLOATING_NONE_IMMEDIATE_COUPON', 'SNOWBALL_FLOATING_CALL', 'SNOWBALL_FLOATING_PUT',
                        'SNOWBALL_FLOATING_NONE', 'SNOWBALL_FLOATING_PARTIAL', 'SNOWBALL_DISCOUNT_OPEN',
                        'SNOWBALL_DOUBLE_DIGITAL', 'SNOWBALL_PARACHUTE', 'SNOWBALL_DISCOUNT_OPEN_PARTIAL_PROTECTIVE']:
        participation_ratio = product['participation']
        participation_ratio1 = 0
        participation_ratio2 = 0
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 跨式
    elif product_type == 'STRADDLE':
        participation_ratio = product['participation']
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation']
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 宽跨式
    elif product_type == 'STRANGLE':
        participation_ratio = 1
        participation_ratio1 = product['participation2']
        participation_ratio2 = product['participation']
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 区间保护看涨
    elif product_type == 'VANILLA_RANGE_PROTECTIVE':
        participation_ratio = 1
        participation_ratio1 = 1
        participation_ratio2 = product['participation']
        participation_ratio3 = product['participation2']
        participation_ratio4 = 1
    # 参与式看涨
    elif product_type == 'VANILLA_PARTICIPATION_CALL':
        participation_ratio = 1
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation2']
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 有限区间保护看涨
    elif product_type == 'VANILLA_LIMIT_RANGE_PROTECTIVE':
        participation_ratio = 1
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation2']
        participation_ratio3 = product['participation3']
        participation_ratio4 = 1
    # 区间保护看涨价差
    elif product_type == 'VANILLA_RANGE_PROTECTIVE_VERTICAL_SPREAD':
        participation_ratio = 1
        participation_ratio1 = 1
        participation_ratio2 = product['participation']
        participation_ratio3 = product['participation2']
        participation_ratio4 = product['participation3']
    # 有限区间保护看涨价差
    elif product_type == 'VANILLA_LIMIT_RANGE_PROTECTIVE_VERTICAL_SPREAD':
        participation_ratio = 1
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation2']
        participation_ratio3 = product['participation3']
        participation_ratio4 = product['participation4']
    # 两段参与式看涨
    elif product_type == 'VANILLA_DOUBLE_PARTICIPATION_CALL':
        participation_ratio = 1
        participation_ratio1 = 1
        participation_ratio2 = product['participation']
        participation_ratio3 = product['participation2']
        participation_ratio4 = 1
    # 参与式看涨价差 自动赎回看涨价差
    elif product_type in ['VANILLA_PARTICIPATION_SPREAD_CALL', 'SNOWBALL_AUTO_REDEEM_SPREAD_CALL']:
        participation_ratio = 1
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation2']
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 增强型雪球 部分保护增强型雪球
    elif product_type in ['SNOWBALL_ENHANCE', 'SNOWBALL_ENHANCE_PARTIAL_PROTECTIVE']:
        participation_ratio = product['participation']
        participation_ratio1 = product['enhancedParticipation']
        participation_ratio2 = 0
        participation_ratio3 = 0
        participation_ratio4 = 0
    # 安全气囊看涨 安全气囊看涨价差 安全气囊看涨保护 安全气囊看涨价差保护
    elif product_type in ['AIRBAG_VANILLA', 'AIRBAG_SPREAD', 'AIRBAG_PROTECTIVE', 'AIRBAG_SPREAD_PROTECTIVE']:
        participation_ratio = 1
        participation_ratio1 = product['participation']
        participation_ratio2 = product['participation2']
        participation_ratio3 = 1
        participation_ratio4 = 0
    elif product_type == 'MODEL_XY':
        participation_ratio = 1
        participation_ratio1 = 0
        participation_ratio2 = 0
        participation_ratio3 = 0
        participation_ratio4 = 0

    return {'participation_ratio': participation_ratio,
            'participation_ratio1': participation_ratio1,
            'participation_ratio2': participation_ratio2,
            'participation_ratio3': participation_ratio3,
            'participation_ratio4':participation_ratio4}


def __handle_touch_price(bct4_trade):
    # 产品结构
    product_type = bct4_trade.get('productType')
    product = bct4_trade['products'][0]
    # 涨跌方向
    call_or_put = product.get('callOrPut')
    low_strike_ratio = None
    high_strike_ratio = None
    low_touch_ratio = None
    high_touch_ratio = None
    # 欧式香草看涨 美式香草看涨 欧式二元看涨 美式二元触碰(看涨) 美式二元不触碰(看涨)
    if (product_type in ['VANILLA_EUROPEAN', 'VANILLA_AMERICAN', 'DIGITAL_EUROPEAN', 'TOUCH', 'NO_TOUCH']
            and call_or_put == 'CALL' or (product_type in ['RANGE_ACCRUALS_DIGITAL_PUT', 'ASIAN_CALL'])):
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = 0
        high_touch_ratio = 0
    # 欧式香草看跌 美式香草看跌 欧式二元看跌 美式二元触碰(看跌) 美式二元不触碰(看跌) 跨式 亚式看跌
    elif (product_type in ['VANILLA_EUROPEAN', 'VANILLA_AMERICAN', 'DIGITAL_EUROPEAN', 'TOUCH', 'NO_TOUCH']
            and call_or_put == 'PUT' or (product_type in ['STRADDLE', 'ASIAN_PUT'])):
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = 0
        low_touch_ratio = 0
        high_touch_ratio = 0
    # 欧式价差 宽跨式 参与式看涨 二元凹式 二元凸式
    # 三层阶梯看涨 三层阶梯看跌 区间累积 保护型看涨
    elif product_type in ['VERTICAL_SPREAD', 'STRANGLE', 'VANILLA_PARTICIPATION_CALL', 'CONCAVE', 'CONVEX',
                          'DOUBLE_DIGITAL_CALL', 'DOUBLE_DIGITAL_PUT', 'RANGE_ACCRUALS', 'PROTECTIVE_CALL']:
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = 0
        high_touch_ratio = 0
    # 参与式看涨价差 四层阶梯看涨 四层阶梯看跌
    elif product_type in ['VANILLA_PARTICIPATION_SPREAD_CALL', 'TRIPLE_DIGITAL_CALL', 'TRIPLE_DIGITAL_PUT']:
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = product['strikePriceRatio3']
        high_touch_ratio = 0
    # 美式双触碰 美式双不触碰
    elif product_type in ['DOUBLE_TOUCH', 'DOUBLE_NO_TOUCH']:
        low_strike_ratio = 0
        high_strike_ratio = 0
        low_touch_ratio = product['strikePriceRatio']
        high_touch_ratio = product['strikePriceRatio2']
    # 区间保护看涨
    elif product_type in ['VANILLA_RANGE_PROTECTIVE']:
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['strikePriceRatio2']
        high_touch_ratio = 100
    # 有限区间保护看涨
    elif product_type in ['VANILLA_LIMIT_RANGE_PROTECTIVE']:
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = product['strikePriceRatio3']
        high_touch_ratio = 100
    # 区间保护看涨价差
    elif product_type in ['VANILLA_RANGE_PROTECTIVE_VERTICAL_SPREAD']:
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['strikePriceRatio2']
        high_touch_ratio = product['strikePriceRatio3']
    # 有限区间保护看涨价差
    elif product_type in ['VANILLA_LIMIT_RANGE_PROTECTIVE_VERTICAL_SPREAD']:
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = product['strikePriceRatio3']
        high_touch_ratio = product['strikePriceRatio4']
    # 两段参与式看涨
    elif product_type in ['VANILLA_DOUBLE_PARTICIPATION_CALL']:
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['strikePriceRatio2']
        high_touch_ratio = 100
    # 鹰式
    elif product_type in ['EAGLE']:
        low_strike_ratio = product['strikePriceRatio4']
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['strikePriceRatio3']
        high_touch_ratio = product['strikePriceRatio2']
    # 向下敲入看涨
    elif product_type in ['KNOCK_IN_DOWN_CALL']:
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = 0
    # 向下敲入看跌
    elif product_type == 'KNOCK_IN_DOWN_PUT':
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = 0
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = 0
    # 单层区间累积看涨
    elif product_type == 'RANGE_ACCRUALS_DIGITAL_CALL':
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = 100
        low_touch_ratio = 0
        high_touch_ratio = 0
    # 单鲨看涨
    elif product_type == 'SHARK_FIN' and call_or_put == 'CALL':
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = 0
        high_touch_ratio = product['knockOutPriceRatio']
    # 单鲨看跌
    elif product_type == 'SHARK_FIN' and call_or_put == 'PUT':
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = 0
        low_touch_ratio = product['knockOutPriceRatio']
        high_touch_ratio = 0
    # 双鲨
    elif product_type == 'DOUBLE_SHARK_FIN':
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = product['knockOutPriceRatio']
        high_touch_ratio = product['knockOutPriceRatio2']
    # 区间增强
    elif product_type == 'RANGE_ENHANCE':
        low_strike_ratio = product['strikePriceRatio2']
        high_strike_ratio = 0
        low_touch_ratio = product['strikePriceRatio']
        high_touch_ratio = 0
    # 自动赎回看涨价差
    elif product_type == 'SNOWBALL_AUTO_REDEEM_SPREAD_CALL':
        low_strike_ratio = product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio2']
        low_touch_ratio = 0
        high_touch_ratio = product['knockOutPriceRatio']
    # 部分保本凤凰到期派息 部分保本凤凰立即派息 部分保本雪球 部分保护增强型雪球 部分保护折价建仓雪球
    elif product_type in ['PHOENIX_FLOATING_PARTIAL_DUE_COUPON', 'PHOENIX_FLOATING_PARTIAL_IMMEDIATE_COUPON',
                          'SNOWBALL_FLOATING_PARTIAL', 'SNOWBALL_ENHANCE_PARTIAL_PROTECTIVE',
                          'SNOWBALL_DISCOUNT_OPEN_PARTIAL_PROTECTIVE']:
        low_strike_ratio = product['knockInMinCouponRate'] + product['strikePriceRatio']
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = product['knockOutPriceRatio']
    # 非保本凤凰到期派息 非保本凤凰立即派息 非保本雪球 折价建仓雪球 降落伞雪球 增强型雪球
    elif product_type in ['PHOENIX_FLOATING_NONE_DUE_COUPON', 'PHOENIX_FLOATING_NONE_IMMEDIATE_COUPON',
                          'SNOWBALL_FLOATING_NONE', 'SNOWBALL_DISCOUNT_OPEN', 'SNOWBALL_PARACHUTE', 'SNOWBALL_ENHANCE']:
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = product['knockOutPriceRatio']
    # 保本雪球看涨
    elif product_type in ['SNOWBALL_FLOATING_CALL']:
        low_strike_ratio = 0
        high_strike_ratio = 0
        low_touch_ratio = 0
        high_touch_ratio = product['knockOutPriceRatio']
    # 保本雪球看跌
    elif product_type in ['SNOWBALL_FLOATING_PUT']:
        low_strike_ratio = 0
        high_strike_ratio = 0
        low_touch_ratio = product['knockOutPriceRatio']
        high_touch_ratio = 0
    # 三元雪球
    elif product_type == 'SNOWBALL_DOUBLE_DIGITAL':
        low_strike_ratio = 0
        high_strike_ratio = 0
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = product['knockOutPriceRatio']
    # 安全气囊看涨
    elif product_type == 'AIRBAG_VANILLA':
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = 100
    # 安全气囊看涨价差
    elif product_type == 'AIRBAG_SPREAD':
        low_strike_ratio = 0
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = product['strikePriceRatio2']
    # 安全气囊看涨保护
    elif product_type == 'AIRBAG_PROTECTIVE':
        low_strike_ratio = product['strikePriceRatio3']
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = 100
    # 安全气囊看涨价差保护
    elif product_type == 'AIRBAG_SPREAD_PROTECTIVE':
        low_strike_ratio = product['strikePriceRatio4']
        high_strike_ratio = product['strikePriceRatio']
        low_touch_ratio = product['knockInPriceRatio']
        high_touch_ratio = product['strikePriceRatio2']
    # 自定义产品
    elif product_type == 'MODEL_XY':
        low_strike_ratio = 0
        high_strike_ratio = 0
        low_touch_ratio = 0
        high_touch_ratio = 0

    return {'low_strike_ratio': low_strike_ratio,
            'high_strike_ratio': high_strike_ratio,
            'low_touch_ratio': low_touch_ratio,
            'high_touch_ratio': high_touch_ratio}


def __handle_rebate(bct4_trade):
    # 产品结构
    product_type = bct4_trade.get('productType')
    product = bct4_trade['products'][0]
    rebate = None
    rebate2 = None
    rebate3 = None

    # 欧式香草 美式香草 欧式价差 跨式 宽跨式
    # 区间保护看涨 参与式看涨 有限区间保护看涨
    # 区间保护看涨价差 有限区间保护看涨价差
    # 两段参与式看涨 参与式看涨价差 鹰式
    # 区间增强 亚式看涨 亚式看跌 安全气囊看涨 安全气囊看涨价差
    # 安全气囊看涨保护 安全气囊看涨价差保护 自定义产品
    if product_type in ['VANILLA_EUROPEAN', 'VANILLA_AMERICAN', 'VERTICAL_SPREAD', 'STRADDLE', 'STRANGLE',
                        'VANILLA_RANGE_PROTECTIVE', 'VANILLA_PARTICIPATION_CALL', 'VANILLA_LIMIT_RANGE_PROTECTIVE',
                        'VANILLA_RANGE_PROTECTIVE_VERTICAL_SPREAD', 'VANILLA_LIMIT_RANGE_PROTECTIVE_VERTICAL_SPREAD',
                        'VANILLA_DOUBLE_PARTICIPATION_CALL', 'VANILLA_PARTICIPATION_SPREAD_CALL', 'EAGLE',
                        'RANGE_ENHANCE', 'ASIAN_CALL', 'ASIAN_PUT', 'AIRBAG_VANILLA', 'AIRBAG_SPREAD',
                        'AIRBAG_PARTICIPATION_PROTECTIVE', 'AIRBAG_SPREAD_PROTECTIVE', 'MODEL_XY']:
        rebate = 0
        rebate2 = 0
        rebate3 = 0
    # 向下敲入看涨 向下敲入看跌
    elif product_type in ['KNOCK_IN_DOWN_CALL', 'KNOCK_IN_DOWN_PUT']:
        rebate = product['unKnockInCouponRate'] / product['participation']
        rebate2 = 0
        rebate3 = 0
    # 欧式二元 美式二元触碰 美式二元不触碰 美式双触碰 美式双不触碰 二元凹式
    # 二元凸式 区间累积 保护型看涨
    # 单层区间累积看涨 单层区间累积看跌
    elif product_type in ['DIGITAL_EUROPEAN', 'TOUCH', 'NO_TOUCH', 'DOUBLE_TOUCH', 'DOUBLE_NO_TOUCH', 'CONCAVE',
                          'CONVEX', 'RANGE_ACCRUALS', 'PROTECTIVE_CALL', 'RANGE_ACCRUALS_DIGITAL_CALL',
                          'RANGE_ACCRUALS_DIGITAL_PUT']:
        rebate = product['couponRate'] / product['participation']
        rebate2 = 0
        rebate3 = 0
    # 三层阶梯看涨 三层阶梯看跌
    elif product_type in ['DOUBLE_DIGITAL_CALL', 'DOUBLE_DIGITAL_PUT']:
        rebate = product['couponRate'] / product['participation']  # 收益率1/参与率
        rebate2 = product['couponRate2'] / product['participation']  # 收益率2/参与率
        rebate3 = 0
    # 四层阶梯看涨 四层阶梯看跌
    elif product_type in ['TRIPLE_DIGITAL_CALL', 'TRIPLE_DIGITAL_PUT']:
        rebate = product['couponRate'] / product['participation']  # 收益率1/参与率
        rebate2 = product['couponRate2'] / product['participation']  # 收益率2/参与率
        rebate3 = product['couponRate3'] / product['participation']  # 收益率3/参与率
    # 单鲨 自动赎回看涨价差
    elif product_type in ['SHARK_FIN', 'SNOWBALL_AUTO_REDEEM_SPREAD_CALL']:
        rebate = product['knockOutCouponRate'] / product['participation']  # 敲出收益率/参与率
        rebate2 = 0
        rebate3 = 0
    # 双鲨
    elif product_type == 'DOUBLE_SHARK_FIN':
        rebate = product['knockOutCouponRate'] / product['participation']  # 敲出收益率1/参与率
        rebate2 = product['knockOutCouponRate']  # 敲出收益率1/参与率
        rebate3 = 0
    # 部分保本凤凰到期派息 部分保本凤凰立即派息
    # 非保本凤凰到期派息 非保本凤凰立即派息
    elif product_type in ['PHOENIX_FLOATING_PARTIAL_DUE_COUPON', 'PHOENIX_FLOATING_PARTIAL_IMMEDIATE_COUPON',
                          'PHOENIX_FLOATING_NONE_DUE_COUPON', 'PHOENIX_FLOATING_NONE_IMMEDIATE_COUPON']:
        rebate = product['phoenixCouponRate'] / product['participation']  # 派息收益率/参与率
        rebate2 = 0
        rebate3 = product['knockOutPriceStepByStepAdjustmentRatio'] * -1  # 敲出价格逐级调整系数*（-1）
    # 保本雪球看涨 保本雪球看跌 非保本雪球
    # 部分保本雪球 折价建仓雪球 三元雪球
    # 降落伞雪球 增强型雪球 部分保护增强型雪球
    # 部分保护折价建仓雪球
    elif product_type in ['SNOWBALL_FLOATING_CALL', 'SNOWBALL_FLOATING_PUT', 'SNOWBALL_FLOATING_NONE',
                          'SNOWBALL_FLOATING_PARTIAL', 'SNOWBALL_DISCOUNT_OPEN', 'SNOWBALL_DOUBLE_DIGITAL',
                          'SNOWBALL_PARACHUTE', 'SNOWBALL_ENHANCE', 'SNOWBALL_ENHANCE_PARTIAL_PROTECTIVE',
                          'SNOWBALL_DISCOUNT_OPEN_PARTIAL_PROTECTIVE']:
        rebate = product['knockOutCouponRate'] / product['participation']  # 敲出收益率/参与率
        rebate2 = 0
        rebate3 = product['knockOutPriceStepByStepAdjustmentRatio'] * -1  # 敲出价格逐级调整系数*（-1）

    return {"rebate": rebate,
            "rebate2": rebate2,
            "rebate3": rebate3}


def __init_format(df, ws):
    col_list = df.columns.to_list()
    for i in range(len(col_list)):
        ws.column_dimensions[get_column_letter(i + 1)].width = 15


def __col_width(df, ws, title, col_width):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    ws.column_dimensions[get_column_letter(col_list.index(title) + 1)].width = col_width


def __format(df, ws, title, formatter):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    col = ws[get_column_letter(col_list.index(title)+1)]
    for cell in col:
        cell.number_format = formatter


def do_export():
    if param_trade_ids is not None and len(param_trade_ids) > 0:
        print('交易编号模式')
        trades = get_trades_by_ids(param_trade_ids)
    elif initial_date_begin is not None and initial_date_end is not None:
        print('日期范围模式')
        trades = get_trades_by_date(initial_date_begin, initial_date_end)
    #
    if len(trades) == 0:
        print('未查询到任何交易')
        return

    df1 = pd.DataFrame(trades)
    filename = 'BCT4交易导出MTC_{}.xlsx'.format(datetime.now().strftime("%Y-%m-%d"))
    writer = pd.ExcelWriter(filename, engine='openpyxl')  # , engine='xlsxwriter'
    df1.to_excel(writer, index=False)
    workbook = writer.book
    ws = workbook.active
    # 格式调整
    __init_format(df1, ws)
    __col_width(df1, ws, 'ContractId', 20)
    __format(df1, ws, 'Notamt', numbers.FORMAT_NUMBER_COMMA_SEPARATED1)
    __format(df1, ws, 'TotalPremium', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'MimumPremium', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Premium', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Pratio', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'LstrikeRatio', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'HstrikeRatio', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'LtouchRatio', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'HtouchRatio', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Rebate', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Rebate2', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Rebate3', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Rebate4', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Pratio1', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Pratio2', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'PrePayFee', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Pratio3', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'Pratio4', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'BeginDate', numbers.FORMAT_DATE_XLSX14)
    __format(df1, ws, 'EndDate', numbers.FORMAT_DATE_XLSX14)
    __format(df1, ws, 'DealDate', numbers.FORMAT_DATE_XLSX14)
    __col_width(df1, ws, 'CrossBorder(跨境类型)', 20)
    __col_width(df1, ws, 'CrossBorder(交易货币)', 20)
    __format(df1, ws, 'KnockInDate', numbers.FORMAT_DATE_XLSX14)
    __format(df1, ws, 'ChangeDate', numbers.FORMAT_DATE_XLSX14)
    __format(df1, ws, 'PrePayFeeRevenue', numbers.FORMAT_PERCENTAGE_00)
    __format(df1, ws, 'SalesFee', numbers.FORMAT_PERCENTAGE_00)

    __col_width(df1, ws, 'open_time', 25)
    __col_width(df1, ws, 'unwind_time', 25)
    __col_width(df1, ws, 'settle_time', 25)
    # 6.保存
    writer.save()


if __name__ == '__main__':
    config = Bct4Hoster()
    client = BCT4Client(config.host, config.script_user, config.script_pwd, BCTVer.V_42)
    do_export()
